/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.edu.ifrn.dao;

import br.edu.ifrn.dominio.Pesquisador;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;/**
 *
 * @author Braga
 */
public class PesquisadorDAO {
    public PesquisadorDAO(){

    }

    public void insertPesquisador(Pesquisador p) throws SQLException{
        Connection conn = ConnectionFactory.getConnection();

        String sql = "INSERT INTO PESQUISADOR VALUES (?, ?, ?, ?, ?, ?, ?, ?) ";

        PreparedStatement stm = conn.prepareStatement(sql);

        stm.setString(1, p.getCPF());
        stm.setString(2, p.getTipo_de_Pesq());
        stm.setDate(3, p.getData_Nasc());
        stm.setString(4, p.getNome());
        stm.setString(5, p.getRG());
        stm.setString(6, p.getMatricula());
        stm.setString(7, p.getEndereco());
        stm.setString(8, p.getTelefone());
        //TODO [Demóstenes] - Inserir campo e-mail.

        stm.executeUpdate();
    }

    public void deletePesquisador(String CPF) throws SQLException{
        Connection conn = ConnectionFactory.getConnection();

        String sql = "DELETE FROM PESQUISADOR WHERE CPF = ?";

        PreparedStatement stm = conn.prepareStatement (sql);

        stm.setString(1, CPF);

        stm.executeUpdate();
    }

    public void updatePesquisador(String CPF, Pesquisador p) throws SQLException{
        Connection conn = ConnectionFactory.getConnection();

        String sql = "UPDATE PESQUISADOR SET TIPO_DE_PESQ = ?, SET DATA_NASC = ?," 
                + "SET NOME = ?, SET RG = ?, SET MATRICULA = ?, SET ENDERECO = ?"
                + "SET TELEFONE = ? WHERE CPF = ?";

        PreparedStatement stm = conn.prepareStatement(sql);

        stm.setString(1, p.getTipo_de_Pesq());
        stm.setDate(2, p.getData_Nasc());
        stm.setString(3, p.getNome());
        stm.setString(4, p.getRG());
        stm.setString(5, p.getMatricula());
        stm.setString(6, p.getEndereco());
        stm.setString(7, p.getTelefone());
        stm.setString(8, CPF);

        stm.executeUpdate();
    }

    public LinkedList selectPesquisador() throws SQLException{
        Connection conn = ConnectionFactory.getConnection();
        LinkedList list = new LinkedList();
        ResultSet rs =  null;

        String sql = "SELECT * FROM PESQUISADOR";

        PreparedStatement stm = conn.prepareStatement(sql);

        rs = stm.executeQuery();

        while(rs.next()){
            Pesquisador p = new Pesquisador();

            p.setCPF(rs.getString(1));
            p.setTipo_de_Pesq(rs.getString(2));
            p.setData_Nasc(rs.getDate(3));
            p.setNome(rs.getString(4));
            p.setRG(rs.getString(5));
            p.setMatricula(rs.getString(6));
            p.setEndereco(rs.getString(7));
            p.setTelefone(rs.getString(8));

            list.add(p);
        }

        return list;
    }

    public LinkedList selectPesquisadorByCPF(String CPF) throws SQLException{
        Connection conn = ConnectionFactory.getConnection();
        LinkedList list = new LinkedList();
        ResultSet rs = null;

        String sql = "SELECT * FROM PESQUISADOR WHERE CPF = ?";

        PreparedStatement stm = conn.prepareStatement(sql);

        stm.setString(1, CPF);

        rs = stm.executeQuery();

        while(rs.next()){
            Pesquisador p = new Pesquisador();

            p.setCPF(rs.getString(1));
            p.setTipo_de_Pesq(rs.getString(2));
            p.setData_Nasc(rs.getDate(3));
            p.setNome(rs.getString(4));
            p.setRG(rs.getString(5));
            p.setMatricula(rs.getString(6));
            p.setEndereco(rs.getString(7));
            p.setTelefone(rs.getString(8));

            list.add(p);
        }

        return list;
    }
}
